package de.stalinski.db;

import de.stalinski.util.Logging;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class BookingsConnector
{

    private static String _sUsr = "root";

    private static String _sPwd = "admin";

    private static String _sDB = "frbrUsers";

    private static String _sTable = "bookings";

    private static Connection _connection;

    private static String _sHost = "localhost";

    private static String _sConn = "jdbc:mysql://" + _sHost + ":3306/" + _sDB;

    public BookingsConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
    }

    public String weekdayAnalysis() {
        String out = "<table class=\"Prices\" cellspacing=0>";
        out += "<thead><th>Wochentag</th><th>Gesamtverbrauch</th></thead>";
        out += "<tbody>";

        String[] days = new String[]{ "Sonntag", "Montag", "Dienstag",
            "Mittwoch", "Donnerstag", "Freitag", "Samstag" };

        Map<Integer, Integer> result = new HashMap<Integer, Integer>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);
            int tmp = 0;
            while ( rs.next() ) {
                try {
                    tmp = result.get(rs.getDate("DATE").getDay()) + rs.getInt(
                            "AMOUNT");
                } catch ( NullPointerException npe ) {
                    Logging.log(Logging.WARNING, npe, this.getClass().getName());
                    tmp = rs.getInt("AMOUNT");
                }
                result.put(rs.getDate("DATE").getDay(), tmp);
            }
            for ( int i = 0; i <= 6; i++ ) {
                out += "<tr>";
                out += "<th scope=\"row\">" + days[i] + "</th>";
                out += "<td>" + result.get(i) + "&nbsp;Flaschen</td>";
                out += "</tr>";
            }
            out += "</tbody></table>";
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( !_connection.isClosed() ) {
                _connection.close();
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;
    }

    public String[] getEstimatedStockEmpty(String sort, int actual, int d) {
        try {
            long days = 0;
            String out = "-";
            ArrayList<Date> dates = new ArrayList<Date>();
            Date today = new Date(System.currentTimeMillis());
            SimpleDateFormat df = new SimpleDateFormat("EEEE, dd.MM.yyyy");
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable + " WHERE SORT='" + sort + "'");
            double total = 0.0;
            double perDay = 0.0;
            Date firstDate = null;
            boolean dateSet = false;
            while ( rs.next() ) {
                total += rs.getDouble("AMOUNT");
                if ( !dateSet && rs.getDate("DATE") != null ) {
                    firstDate = rs.getDate("DATE");
                    dateSet = true;
                }
                if ( !dates.contains(rs.getDate("DATE")) ) {
                    dates.add(rs.getDate("DATE"));
                }
            }
            long diff = 0;
            long goneDays = 0;
            if ( dateSet ) {
                diff = (today.getTime() - firstDate.getTime()) + (24 * 60 * 60 * 1000);
                goneDays = diff / (24 * 60 * 60 * 1000);
            }
            if ( goneDays > 0 ) {
                perDay = total / goneDays;
            } else {
                perDay = 0.0;
            }
            if ( perDay > 0 ) {
                days = (long) ((actual * 1.0) / perDay);
            } else {
                days = Integer.MAX_VALUE;
            }
            if ( days < Integer.MAX_VALUE ) {
                today = new Date(System.currentTimeMillis() + (days * 24 * 60 * 60 * 1000));
                out = df.format(today);
            }
            int toOrder = (int) ((perDay * d) / new PricesTableConnector().
                    getBottlesPerBox(sort));
            String advice = "-";
            String append = "";
            if ( toOrder > 0 ) {
                if ( toOrder > 1 ) {
                    append = "n";
                }
                advice = toOrder + " Kiste" + append;
            }
            return new String[]{ out, advice };
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return null;
    }

    public void book(String fn, String sn, double value) {
        Date date = new Date(System.currentTimeMillis());
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat tf = new SimpleDateFormat("HH:mm:ss");
        String timeString = tf.format(date);
        String dateString = df.format(date);
        String id = "";
        String un = "";
        boolean valid = true;
        try {
            un = new ConsumersConnector().getUsername(fn, sn);
            id = new ConsumersConnector().getId(fn, sn, un);
        } catch ( NullPointerException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
            valid = false;
        }

        if ( valid ) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
                Statement st = null;
                st = _connection.createStatement();
                String executeValues = "('" + id + "','" + dateString + "','" + timeString + "','" + fn + "','" + sn + "','" + un + "','EINZAHLUNG'," + 0 + "," + 0 + "," + value + ")";
                String execute = "INSERT INTO " + _sTable + " (UID,DATE,TIME,VORNAME, NACHNAME, USER, SORT, AMOUNT, CURRENTPRICE, VALUE)" + "VALUES " + executeValues;
                st.execute(execute);
                _connection.close();
            } catch ( SQLException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            } catch ( ClassNotFoundException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
        }
    }

    public BigDecimal getPaidFor(String ID, String fn, String sn) {
        BigDecimal result = new BigDecimal(0.0);
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable + " WHERE VORNAME='" + fn + "' AND NACHNAME='" + sn + "'");
            BigDecimal cache = null;
            while ( rs.next() ) {
                if ( rs.getString("UID").equals(ID) && rs.getString("SORT").
                        equals("EINZAHLUNG") ) {
                    cache = new BigDecimal(rs.getDouble("VALUE"));
                    result = result.add(cache).
                            setScale(2, BigDecimal.ROUND_CEILING);
                }
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return result;
    }

    public BigDecimal getLiabilityFor(String ID, String fn, String sn) {
        BigDecimal result = new BigDecimal(0.0);
        try {

            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable + " WHERE VORNAME='" + fn + "' AND NACHNAME='" + sn + "'");
            BigDecimal cache = null;
            while ( rs.next() ) {
                if ( rs.getString("UID").equals(ID) && !rs.getString("SORT").
                        equals("EINZAHLUNG") ) {
                    cache = new BigDecimal(rs.getDouble("VALUE"));
                    result = result.add(cache).
                            setScale(2, BigDecimal.ROUND_CEILING);
                }
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return result;
    }

    public String getConsumptionFor(String from, String to, String consumer) {
        String out = "";
        try {
            out += "<table class=\"Prices\" cellspacing=0>";
            out += "<thead>";
            out += "<th>Datum</th>";
            out += "<th>Kunde</th>";
            out += "<th>Sorte</th>";
            out += "<th>Anzahl</th>";
            out += "<th>Kosten</th>";
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            String fn;
            String sn;
            if ( consumer == null ) {
                consumer = "Alle";
            }
            if ( consumer.equals("Alle") ) {
                rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' ORDER BY DATE,TIME ASC");
            } else {
                sn = consumer.substring(0, consumer.indexOf(","));
                fn = consumer.substring(consumer.indexOf(",") + 2, consumer.
                        length());
                rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' AND VORNAME='" + fn + "'" + "AND Nachname='" + sn + "' ORDER BY DATE ASC");
            }
            double total = 0.0;
            double received = 0.0;
            while ( rs.next() ) {
                String date = rs.getString("DATE");
                fn = rs.getString("VORNAME");
                sn = rs.getString("NACHNAME");
                String sort = rs.getString("SORT");
                int amnt = rs.getInt("AMOUNT");
                Double val = rs.getDouble("VALUE");
                if ( !sort.equals("EINZAHLUNG") ) {
                    total += val;
                    out += "<tr bgcolor=\"#FFFFFF\">";
                    out += "<td>" + date + "</td>";
                    out += "<td>" + sn + ", " + fn + "</td>";
                    out += "<td>" + sort + "</td>";
                    out += "<td>" + amnt + "</td>";
                    out += "<td>" + new BigDecimal(val).setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td>";
                    out += "</tr>";
                } else if ( sort.equals("EINZAHLUNG") ) {
                    received += val;
                }
            }
            //---------------------------------------------------------
            rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' ORDER BY SORT ASC");
            ArrayList<String> sorts = new ArrayList<String>();
            while ( rs.next() ) {
                if ( !sorts.contains(rs.getString("SORT")) && !rs.getString("SORT").
                        equals("EINZAHLUNG") ) {
                    sorts.add(rs.getString("SORT"));
                }
            }
            out += "<table class=\"Prices\" cellspacing=0>";
            out += "<thead>";
            out += "<td></td>";
            out += "<th>Verbrauch</th>";
            out += "<th>Einnahmen</th>";
            out += "<th>Anteil</th>";
            out += "<th>Verzehr / Tag</th>";
            out += "</thead>";
            out += "<tbody>";
            int amount = 0;
            double partial = 0.0;
            double percent = 0.0;
            double perDay = 0.0;
            ArrayList<String> dates = new ArrayList<String>();
            ArrayList<Integer> amounts = new ArrayList<Integer>();
            int amntForSort = 0;
            for ( String s : sorts ) {
                if ( consumer.equals("Alle") ) {
                    rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' AND SORT='" + s + "' ORDER BY SORT ASC");
                } else {
                    sn = consumer.substring(0, consumer.indexOf(","));
                    fn = consumer.substring(consumer.indexOf(",") + 2, consumer.
                            length());
                    rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' AND SORT='" + s + "' " + "AND VORNAME='" + fn + "' AND Nachname='" + sn + "' ORDER BY SORT ASC");
                }
                while ( rs.next() ) {
                    amntForSort += rs.getInt("AMOUNT");
                }
                amounts.add(amntForSort);
                amntForSort = 0;
            }
            int maximum = 0;
            HashMap<String, Integer> sortAmounts = new HashMap<String, Integer>();
            HashMap<String, Integer> sortPerDay = new HashMap<String, Integer>();
            for ( String s : sorts ) {
                if ( consumer.equals("Alle") ) {
                    rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' AND SORT='" + s + "' ORDER BY SORT ASC");
                } else {
                    sn = consumer.substring(0, consumer.indexOf(","));
                    fn = consumer.substring(consumer.indexOf(",") + 2, consumer.
                            length());
                    rs = st.executeQuery("select * from " + _sTable + " WHERE DATE <= '" + to + "' AND DATE >='" + from + "' AND SORT='" + s + "' " + "AND VORNAME='" + fn + "' AND Nachname='" + sn + "' ORDER BY SORT ASC");
                }
                while ( rs.next() ) {
                    amount += rs.getInt("AMOUNT");
                    partial += rs.getDouble("VALUE");
                    if ( !dates.contains(rs.getString("DATE")) ) {
                        dates.add(s);
                    }
                }
                if ( amount >= maximum ) {
                    maximum = amount;
                }
                percent = Math.round(((partial / total) * 100.0) * 100.0) / 100.0;
                if ( dates.size() > 0 ) {
                    perDay = Math.round(amount / dates.size());
                }
                partial = Math.round(partial * 100.0) / 100.0;
                out += "<tr>";
                out += "<th scope=\"row\">" + s + "</th>";
                out += "<td>" + amount + "</td>";
                out += "<td>" + new BigDecimal(partial).setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td>";
                out += "<td>" + new BigDecimal(percent).setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&#37;</td>";
                out += "<td>" + new BigDecimal(perDay).setScale(2, BigDecimal.ROUND_CEILING) + "</td>";
                out += "</tr>";
                sortAmounts.put(s, amount);
                sortPerDay.put(s, (int) perDay);
                amount = 0;
                partial = 0.0;
                perDay = 0;
                dates.clear();
            }
            out += "</tbody>";
            out += "</table>";
            //---------------------------------------------------------
            total = Math.round(total * 100.0) / 100.0;
            out += "</tbody></table>";
            out += "<table class=\"Prices\" cellspacing=0>";
            out += "<thead>";
            out += "<th colspan=2>Übersicht</th>";
            out += "</thead>";
            out += "<tbody>";
            out += "<tr><th scope=\"row\">Umsatz:</th><td>" + new BigDecimal(total).
                    setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td></tr>";
            out += "<tr><th scope=\"row\">Erhalten:</th><td>" + new BigDecimal(received).
                    setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td></tr>";
            out += "<tr><th scope=\"row\">Offen:</th><td>" + new BigDecimal(total).
                    subtract(new BigDecimal(received)).
                    setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td></tr>";
            out += "</tbody></table>";
            _connection.close();
            return out;
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( !_connection.isClosed() ) {
                _connection.close();
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        if(!out.contains("</table>")) {
            try {
                throw new Exception("Tabelle enthält keinen </table> - Tag");
            } catch ( Exception ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
        }
        return out;
    }

}
